﻿Imports System.Data.SqlClient
Imports Bussines.entity
Imports System.Data.SqlTypes

Public Class invitacionDAL
    Private conexion As New conexion
    Public Function GrabarInvitacion(ByVal BEinvitacion As invitacionBE) As String
        Dim cmd As New SqlCommand
        Dim cmd2 As New SqlCommand
        Dim intIdInvitacion As Integer
        Dim cnn As SqlConnection = conexion.abrir
        Dim trx As SqlTransaction = cnn.BeginTransaction(IsolationLevel.Serializable)
        cmd.Connection = cnn
        cmd.Transaction = trx
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pdo_sp_grabarInvitacion"
        cmd.Parameters.Add(New SqlParameter("@idInvitacion", SqlDbType.Int)).Value = BEinvitacion.idInvitacion
        cmd.Parameters.Add(New SqlParameter("@Invitacion_num", SqlDbType.VarChar, 15)).Value = BEinvitacion.Invitacion_Num
        cmd.Parameters.Add(New SqlParameter("@id_Asunto", SqlDbType.Int)).Value = BEinvitacion.id_Asunto
        cmd.Parameters.Add(New SqlParameter("@idCompania", SqlDbType.Int)).Value = BEinvitacion.idCompania
        cmd.Parameters.Add(New SqlParameter("@equipo_id", SqlDbType.VarChar, 10)).Value = BEinvitacion.equipo_Id
        cmd.Parameters.Add(New SqlParameter("@idArea", SqlDbType.Char, 6)).Value = BEinvitacion.idArea
        cmd.Parameters.Add(New SqlParameter("@idLugar", SqlDbType.Char, 8)).Value = BEinvitacion.idLugar
        cmd.Parameters.Add(New SqlParameter("@otroLugar", SqlDbType.VarChar, 200)).Value = BEinvitacion.otroLugar
        cmd.Parameters.Add(New SqlParameter("@Moderador", SqlDbType.VarChar, 8)).Value = BEinvitacion.Moderador
        cmd.Parameters.Add(New SqlParameter("@fecha", SqlDbType.VarChar)).Value = BEinvitacion.Fecha
        'cmd.Parameters.Add(New SqlParameter("@semana", SqlDbType.VarChar)).Value = BEinvitacion.Semana
        cmd.Parameters.Add(New SqlParameter("@inicio", SqlDbType.VarChar)).Value = BEinvitacion.Inicio
        cmd.Parameters.Add(New SqlParameter("@fin", SqlDbType.VarChar)).Value = BEinvitacion.Fin
        cmd.Parameters.Add(New SqlParameter("@duracion", SqlDbType.VarChar)).Value = BEinvitacion.Duracion
        'cmd.Parameters.Add(New SqlParameter("@secretario", SqlDbType.VarChar)).Value = BEinvitacion.Secretario
        cmd.Parameters.Add(New SqlParameter("@Facilitador", SqlDbType.VarChar)).Value = BEinvitacion.Facilitador
        cmd.Parameters.Add(New SqlParameter("@activo", SqlDbType.Int)).Value = BEinvitacion.Activo
        cmd.Parameters.Add(New SqlParameter("@idInvitacionSalida", SqlDbType.Int)).Direction = ParameterDirection.Output
        cmd.Parameters.Add(New SqlParameter("@usuario_creado", SqlDbType.VarChar)).Value = BEinvitacion.usuario_creado
        cmd.Parameters.Add(New SqlParameter("@usuario_modificado", SqlDbType.VarChar)).Value = BEinvitacion.usuario_modificado
        cmd.Parameters.Add(New SqlParameter("@observacion", SqlDbType.VarChar, -1)).Value = BEinvitacion.observacion

        Try
            cmd.ExecuteNonQuery()
            intIdInvitacion = Convert.ToInt32(cmd.Parameters("@idInvitacionSalida").Value.ToString())

            For Each invitacionAsistencia In BEinvitacion.ListaAsistente
                cmd2 = New SqlCommand
                cmd2.Connection = cnn
                cmd2.Transaction = trx
                cmd2.CommandType = CommandType.StoredProcedure
                cmd2.CommandText = "sp_insertar_AsistenteInvitacion"
                cmd2.Parameters.Add(New SqlParameter("@idCorrelativo", SqlDbType.Int)).Value = invitacionAsistencia.idCorrelativo
                cmd2.Parameters.Add(New SqlParameter("@idAsistente", SqlDbType.VarChar)).Value = invitacionAsistencia.idAsistente
                cmd2.Parameters.Add(New SqlParameter("@idInvitacion", SqlDbType.Int)).Value = intIdInvitacion
                cmd2.Parameters.Add(New SqlParameter("@nombre", SqlDbType.VarChar)).Value = invitacionAsistencia.nombre
                cmd2.Parameters.Add(New SqlParameter("@cargo", SqlDbType.VarChar)).Value = invitacionAsistencia.cargo
                cmd2.Parameters.Add(New SqlParameter("@activo", SqlDbType.Int)).Value = invitacionAsistencia.activo
                cmd2.ExecuteNonQuery()
                cmd2.Dispose()
            Next

            trx.Commit()
            trx.Dispose()
            cnn.Close()
            cnn.Dispose()
            Return intIdInvitacion.ToString()
        Catch ex As Exception
            trx.Rollback()
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
                cnn.Dispose()
            End If
            Return ex.Message
        End Try
    End Function

    Public Function ListarInvitacionXInvitacion(ByVal intCodInvitacion As Integer)
        Dim dtInvitacion As New DataTable
        Dim cmd As New SqlCommand
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "PDO_SP_LISTARINVITACIONXINVITACION"
        cmd.Parameters.Add(New SqlParameter("@IDINVITACION", SqlDbType.Int)).Value = intCodInvitacion
        Try
            cmd.Connection = conexion.abrir
            dtInvitacion.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            cmd.Dispose()
            Return dtInvitacion
        Catch ex As Exception
            Return dtInvitacion
        End Try
    End Function

    Public Function listarAsistentesInvitacion_mail(ByVal intCodInvitacion As Integer) As DataTable
        Dim dtInvitacionAsist As New DataTable
        Dim cmd As New SqlCommand
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pdo_sp_InvitacionAsistentes_mailCitas"
        cmd.Parameters.Add(New SqlParameter("@idInvitacion", SqlDbType.Int)).Value = intCodInvitacion
        Try
            cmd.Connection = conexion.abrir
            dtInvitacionAsist.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            cmd.Dispose()
            Return dtInvitacionAsist
        Catch ex As Exception
            Return dtInvitacionAsist
        End Try
    End Function


    Public Function listarInvitacionCabecera_mail(ByVal intCodInvitacion As Integer) As DataTable
        Dim dtInvitacion As New DataTable
        Dim cmd As New SqlCommand
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pdo_sp_InvitacionAsistentes_Cabecera_mailCitas"
        cmd.Parameters.Add(New SqlParameter("@idInvitacion", SqlDbType.Int)).Value = intCodInvitacion
        Try
            cmd.Connection = conexion.abrir
            dtInvitacion.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            cmd.Dispose()
            Return dtInvitacion
        Catch ex As Exception
            Return dtInvitacion
        End Try
    End Function

    Public Function ActualizarIDSharepoint(ByVal intIdInvitacion As Integer, ByVal intIdSharepoint As Integer)
        Dim dtInvitacion As New DataTable
        Dim cmd As New SqlCommand
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pdo_sp_InvitacionActualizarIDSharepoint"
        cmd.Parameters.Add(New SqlParameter("@idInvitacion", SqlDbType.Int)).Value = intIdInvitacion
        cmd.Parameters.Add(New SqlParameter("@idSharepointInvita", SqlDbType.Int)).Value = intIdSharepoint
        Try
            cmd.Connection = conexion.abrir
            dtInvitacion.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            cmd.Dispose()
            Return dtInvitacion
        Catch ex As Exception
            Return dtInvitacion
        End Try
    End Function

    Public Function grupoSharepointXInvitacion(ByVal intIdInvitacion As Integer) As String
        Dim dtInvitacion As New DataTable
        Dim cmd As New SqlCommand
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pdo_sp_grupoSharepointXInvitacion"
        cmd.Parameters.Add(New SqlParameter("@idInvitacion", SqlDbType.Int)).Value = intIdInvitacion
        Try
            cmd.Connection = conexion.abrir
            Dim strGrupo As String = cmd.ExecuteScalar().ToString()
            conexion.Cerrar()
            cmd.Dispose()
            Return strGrupo
        Catch ex As Exception
            Throw ex
        End Try
    End Function


    Public Function ListarUltimasInvitaciones()
        Dim dtInvitacion As New DataTable
        Dim cmd As New SqlCommand
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pdo_sp_ListarUltimasInvitacion"
        Try
            cmd.Connection = conexion.abrir
            dtInvitacion.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            cmd.Dispose()
            Return dtInvitacion
        Catch ex As Exception
            Return dtInvitacion
        End Try
    End Function

    Public Function ListarBusquedaInvitaciones(ByVal idEquipo As String, ByVal Asunto As String) As DataTable

        Dim dtInvitacion As New DataTable
        Dim cmd As New SqlCommand
        Dim idEquip As New SqlString
        If idEquipo = "" Then
            idEquip = SqlString.Null
        Else
            idEquip = idEquipo
        End If

        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pdo_sp_ListarBusquedaInvitacion"
        cmd.Parameters.Add(New SqlParameter("@idEquipo", SqlDbType.VarChar)).Value = idEquip
        cmd.Parameters.Add(New SqlParameter("@Asunto", SqlDbType.VarChar)).Value = Asunto
        Try
            cmd.Connection = conexion.abrir
            dtInvitacion.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            cmd.Dispose()
            Return dtInvitacion
        Catch ex As Exception
            Return dtInvitacion
        End Try
    End Function

    Public Function ListarInvitacionXId(ByVal idInvitacion As Integer) As DataTable
        Dim dtInvitacion As New DataTable
        Dim cmd As New SqlCommand

        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pdo_sp_listar_InvitacionXID"
        cmd.Parameters.Add(New SqlParameter("@idInvitacion", SqlDbType.Int)).Value = idInvitacion
        Try
            cmd.Connection = conexion.abrir
            dtInvitacion.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            cmd.Dispose()
            Return dtInvitacion
        Catch ex As Exception
            Return dtInvitacion
        End Try
    End Function

    Public Function ListarInvitacionXId_VistaPrevia(ByVal idInvitacion As Integer) As DataTable
        Dim dtInvitacion As New DataTable
        Dim cmd As New SqlCommand

        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pdo_sp_listar_InvitacionXID_VistaPrevia"
        cmd.Parameters.Add(New SqlParameter("@idInvitacion", SqlDbType.Int)).Value = idInvitacion
        Try
            cmd.Connection = conexion.abrir
            dtInvitacion.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            cmd.Dispose()
            Return dtInvitacion
        Catch ex As Exception
            Return dtInvitacion
        End Try
    End Function

    Public Function BuscarInvitacionXId(ByVal idInvitacion As Integer) As DataTable
        Dim dtInvitacion As New DataTable
        Dim cmd As New SqlCommand

        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pdo_sp_buscarInvitacionXId"
        cmd.Parameters.Add(New SqlParameter("@idInvitacion", SqlDbType.Int)).Value = idInvitacion
        Try
            cmd.Connection = conexion.abrir
            dtInvitacion.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            cmd.Dispose()
            Return dtInvitacion
        Catch ex As Exception
            Return dtInvitacion
        End Try
    End Function


    Public Function ListarInvitacionXAsistentes(ByVal idInvitacion As Integer) As DataTable
        Dim dtInvitacion As New DataTable
        Dim cmd As New SqlCommand

        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pdo_sp_listar_InvitacionXAsistentes"
        cmd.Parameters.Add(New SqlParameter("@idInvitacion", SqlDbType.Int)).Value = idInvitacion
        Try
            cmd.Connection = conexion.abrir
            dtInvitacion.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            cmd.Dispose()
            Return dtInvitacion
        Catch ex As Exception
            Return dtInvitacion
        End Try
    End Function

    Public Function ListarInvitacionXAsistentes_Invitacion(ByVal idInvitacion As Integer) As DataTable
        Dim dtInvitacion As New DataTable
        Dim cmd As New SqlCommand

        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pdo_sp_listar_InvitacionXAsistentes_invitacion"
        cmd.Parameters.Add(New SqlParameter("@idInvitacion", SqlDbType.Int)).Value = idInvitacion
        Try
            cmd.Connection = conexion.abrir
            dtInvitacion.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            cmd.Dispose()
            Return dtInvitacion
        Catch ex As Exception
            Return dtInvitacion
        End Try
    End Function


    Public Function obtenerCodigoInvitacionxidSharepoint(ByVal idSharepointInv As Integer) As Integer
        Dim cmd As New SqlCommand
        Dim intIdInvitacion As Integer
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pdo_sp_obtenerIdInvitacionXidSharepoint"
        cmd.Parameters.Add(New SqlParameter("@idInvitacionSharepoint", SqlDbType.Int)).Value = idSharepointInv
        Try
            cmd.Connection = conexion.abrir
            intIdInvitacion = Integer.Parse(cmd.ExecuteScalar.ToString())
            conexion.Cerrar()
            cmd.Dispose()
            Return intIdInvitacion
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Sub BloquearInvitacion(ByVal idInvitacion As Integer)
        Dim cmd As New SqlCommand
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pdo_sp_BloquearInvitacion"
        cmd.Parameters.Add(New SqlParameter("@idInvitacion", SqlDbType.Int)).Value = idInvitacion
        Try
            cmd.Connection = conexion.abrir
            cmd.ExecuteNonQuery()
            conexion.Cerrar()
            cmd.Dispose()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub


    Public Function IdSharepointInvitacion(ByVal idInvitacion As Integer) As Integer
        Dim cmd As New SqlCommand
        Dim intIdSharepoint As Integer
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pdo_sp_IdSharepointInvitacion"
        cmd.Parameters.Add(New SqlParameter("@idInvitacion", SqlDbType.Int)).Value = idInvitacion
        Try
            cmd.Connection = conexion.abrir

            intIdSharepoint = Convert.ToInt32(cmd.ExecuteScalar().ToString())
            conexion.Cerrar()
            cmd.Dispose()
            Return intIdSharepoint
        Catch ex As Exception
            Throw ex
        End Try
    End Function

End Class
